<?php

namespace App\V3\Api\Services;

use App\V3\Api\Models\LogOutStorageModel;
use App\V3\Api\Models\LogPackageModel;
use App\V3\Api\Models\MaterielContentModel;
use App\V3\Api\Models\MaterielModel;
use App\V3\Api\Models\MaterielsTypeModel;
use App\V3\Api\Models\SellGoodsDetailModel;
use App\V3\Api\Models\SellModel;
use App\V3\Api\Models\SellPackageContentModel;
use App\V3\Api\Models\SellPackageDetailModel;
use App\V3\Api\Models\SellPayRecordModel;
use App\V3\Api\Models\StorageManageModel;
use App\V3\Api\Models\StorageModel;
use App\V3\Api\Models\TallyModel;
use App\V3\V3_1\Models\MaterielPayHistoryModel;
use App\V3\V3_1\Models\SalesReturnModel;
use App\V3\V3_1\Models\SellGoodsDetail;
use Illuminate\Support\Facades\DB;
use App\V3\Api\Models\SellGoodsListModel;
use App\V3\Api\Models\SupplierCustomerModel;

class StatisticsService
{
    static public function sell($data, $date, $condition, $condition1)
    {
        $tallyModel = SellGoodsDetail::select(DB::raw("sum(num) as num,image,name,sum((price-cost_price)*num) as profit,sum(price*num) as total_price"))
            ->where($condition)
            ->whereBetween('sell_date',$date)
            ->groupBy('name','type_name')->get();

        $packageInfo = SellPackageDetailModel::select(
            DB::raw("sum(num) as num,sum((price-total_cost)*num) as profit,sum(price*num) as total_price"),
            'b.package_name'
        )
            ->from('sell_package_detail as a')
            ->leftJoin('package as b', 'b.id', '=', 'a.package_id')
            ->with('packageContent', function($query) {
                $query->select(
                    'a.package_detail_id'
                )
                    ->from('sell_package_content as a')
                    ->leftJoin('materiel_class as b', 'b.id', '=', 'a.materiel_class_id')
                    ->leftJoin('materiel_class as c', 'b.id', '=', 'b.parent_id')
                    ->leftJoin('materiel_type as d', 'd.id', '=', 'a.type_id')
                    ->leftJoin('storage as e', 'e.id', '=', 'a.storage_id')
                    ->leftJoin('storage_manage as f', 'f.id', '=', 'a.storage_manage_id');
            })
            ->where($condition1)
            ->whereBetween('sell_date',$date)
            ->groupBy('package_id')->get();
    }

    //采购报表
    static public function statementMateriel($condition, $data)
    {
         $amountInfo = MaterielContentModel::selectRaw('
            SUM(num) as total_amount
        ')
            ->from('materiel_content as a')
            ->leftJoin('materiel as b', 'b.id', '=', 'a.materiel_id')
            ->where('b.repeal_status', 0)
            ->where($condition)
            ->where(function($query) {
                $query->orWhere('b.pay_status', '!=', 0);
                $query->orWhere('b.in_storage_status', '!=', 0);
            })
            ->groupBy('a.main_id')
            ->first();
        $result['total_amount'] = empty($amountInfo)?0:$amountInfo['total_amount'];

        $priceInfo = MaterielContentModel::selectRaw('
            SUM(num * unit_price) as total_price
        ')
            ->from('materiel_content as a')
            ->leftJoin('materiel as b', 'b.id', '=', 'a.materiel_id')
            ->where('b.repeal_status', 0)
            ->where($condition)
            ->where(function($query) {
                $query->orWhere('b.pay_status', '!=', 0);
                $query->orWhere('b.in_storage_status', '!=', 0);
            })
            ->groupBy('a.main_id')
            ->first();
        $result['total_price'] = empty($priceInfo)?0:$priceInfo['total_price'];

        $result['materiel_content_info'] = MaterielContentModel::select(
                'type_id',
                'materiel_name',
                'type_name',
                'materiel_no',
                'materiel_img',
                DB::raw('SUM(num * unit_price) as total_unit_price'),
                DB::raw('SUM(num) as num')
            )
            ->from('materiel_content as a')
            ->leftJoin('materiel as b', 'b.id', '=', 'a.materiel_id')
            ->where('b.repeal_status', 0)
            ->where($condition)
            ->where(function($query) {
                $query->orWhere('b.pay_status', '!=', 0);
                $query->orWhere('b.in_storage_status', '!=', 0);
            })
            ->groupBy('type_id')->paginate($data['pageSize'],['*'], '',$data['currentPage']);

        return $result;
    }

    //采购报表详细
    static public function statementMaterielDetail($data, $condition)
    {
        $amountInfo = MaterielContentModel::selectRaw('
            SUM(num) as total_amount
        ')
            ->from('materiel_content as a')
            ->leftJoin('materiel as b', 'b.id', '=', 'a.materiel_id')
            ->where('a.type_id', $data['type_id'])
            ->where('b.repeal_status', 0)
            ->where($condition)
            ->where(function($query) {
                $query->orWhere('b.pay_status', '!=', 0);
                $query->orWhere('b.in_storage_status', '!=', 0);
            })
            ->first();
        $result['total_amount'] = empty($amountInfo)?0:$amountInfo['total_amount'];

        $priceInfo = MaterielContentModel::select(
            DB::raw('SUM(num * unit_price) as total_price')
        )
            ->from('materiel_content as a')
            ->leftJoin('materiel as b', 'b.id', '=', 'a.materiel_id')
            ->where('a.type_id', $data['type_id'])
            ->where('b.repeal_status', 0)
            ->where($condition)
            ->where(function($query) {
                $query->orWhere('b.pay_status', '!=', 0);
                $query->orWhere('b.in_storage_status', '!=', 0);
            })
            ->first();
        $result['total_price'] = empty($priceInfo)?0:$priceInfo['total_price'];

        $result['materiel_content_info'] = MaterielContentModel::select(
            'type_id',
            'materiel_name',
            'type_name',
            'materiel_no',
            'materiel_img',
            'unit_price',
            'num',
            'a.purchase_time',
        )
            ->from('materiel_content as a')
            ->leftJoin('materiel as b', 'b.id', '=', 'a.materiel_id')
            ->where('a.type_id', $data['type_id'])
            ->where('b.repeal_status', 0)
            ->where($condition)
            ->where(function($query) {
                $query->orWhere('b.pay_status', '!=', 0);
                $query->orWhere('b.in_storage_status', '!=', 0);
            })
            ->orderBy('a.purchase_time', 'DESC')
            ->paginate($data['pageSize'],['*'], '',$data['currentPage']);
        return $result;
    }

    //成本报表
    static public function statementCost($condition, $data)
    {
        //成本报表类型:0:商品;1:套餐;
        if(!$data['type']) {
            $result = SELF::getSellGoodsInfo($condition, $data);
        }
        else {
            $result = SELF::getSellPackageInfo($condition, $data);
        }
        return $result;
    }

    static private function getSellGoodsInfo($condition, $data)
    {
        //总数量
        $amountInfo = LogOutStorageModel::select(
            DB::raw('SUM(operate_num) as total_amount')
        )
            ->from('log_outstorage as a')
            ->where('is_cancel', 0)
            ->where('type', '!=', 1)
            ->where($condition)
            ->first();

        $result['total_amount'] = empty($amountInfo)?0:$amountInfo['total_amount'];

        //总成本
        $priceInfo = LogOutStorageModel::select(
            DB::raw('SUM(operate_num * materiel_mean_cost) as total_price')
        )
            ->from('log_outstorage as a')
            ->where('is_cancel', 0)
            ->where('type', '!=', 1)
            ->where($condition)
            ->first();

        $result['total_cost'] = empty($priceInfo)?0:sprintf('%.6f', $priceInfo['total_price']);

        //总售价
        $sellPriceInfo = LogOutStorageModel::select(
            DB::raw('SUM(operate_num * sell_price) as total_sell_price')
        )
            ->from('log_outstorage as a')
            ->where('is_cancel', 0)
            ->where('type', '!=', 1)
            ->where($condition)
            ->first();
        $result['total_sell_price'] = empty($sellPriceInfo)?0:sprintf('%.6f', $sellPriceInfo['total_sell_price']);

        //总利润
        $result['total_income'] = sprintf('%.6f',$result['total_sell_price'] -  $result['total_cost']);

        $result['sell_materiel_info'] = LogOutStorageModel::select(
            'materiel_type_id',
            'b.materiel_name',
            'b.type_name',
            'b.materiel_no',
            'b.materiel_img',
            DB::raw('SUM(operate_num) as total_num'),
            DB::raw('SUM(operate_num * sell_price) as total_price'),
            DB::raw('SUM(materiel_mean_cost * operate_num) as total_cost'),
        )
            ->from('log_outstorage as a')
            ->leftJoin('materiel_type as b', 'b.id', '=', 'a.materiel_type_id')
            ->where('is_cancel', 0)
            ->where('type', '!=', 1)
            ->where($condition)
            ->orderBy('out_storage_time', 'DESC')
            ->groupBy('materiel_type_id')
            ->paginate($data['pageSize'],['*'], '',$data['currentPage']);

        return $result;
    }

    static private function getSellPackageInfo($condition, $data)
    {
        //总数量
        $amountInfo = LogPackageModel::select(
            DB::raw('SUM(num) as total_amount'),
        )
            ->from('log_package as a')
            ->where('is_cancel', 0)
            ->where($condition)
            ->first();

        $result['total_amount'] = empty($amountInfo)?0:$amountInfo['total_amount'];

        //总售价
        $priceInfo = LogPackageModel::select(
            DB::raw('SUM(num * sell_price) as total_sell_price'),
        )
            ->from('sell_package_detail as a')
            ->from('log_package as a')
            ->where('is_cancel', 0)
            ->where($condition)
            ->first();

        $result['total_sell_price'] = empty($priceInfo)?0:sprintf('%.6f', $priceInfo['total_sell_price']);

        //总成本
        $totalPrice= LogPackageModel::select(
            DB::raw('SUM(num * cost) as total_price'),
        )
            ->from('sell_package_detail as a')
            ->from('log_package as a')
            ->where('is_cancel', 0)
            ->where($condition)
            ->first();

        $result['total_price'] = empty($totalPrice)?0:sprintf('%.6f', $totalPrice['total_price']);

        $result['total_income'] = sprintf('%.6f', ($result['total_sell_price'] - $result['total_price']));

        $result['sell_materiel_info'] = LogPackageModel::select(
            'a.package_id',
            'b.package_name',
            DB::raw('SUM(num) as total_num'),
            DB::raw('SUM(sell_price * num) as total_price'),
            DB::raw('SUM(cost * num) as total_cost'),
        )
            ->from('log_package as a')
            ->leftJoin('package as b', 'b.id', '=', 'a.package_id')
            ->where('is_cancel', 0)
            ->where($condition)
            ->orderBy('out_storage_time', 'DESC')
            ->groupBy('package_id')
            ->paginate($data['pageSize'],['*'], '',$data['currentPage']);

        return $result;
    }

    //销售报表
    static public function statementSell($condition, $data)
    {
        $moneyInfo = SellModel::select(
            DB::raw('SUM(final_money) as total_money')
        )

            ->where([
                'repeal_status' => 0,
            ])
            ->where($condition)
            ->where(function($query) {
                $query->orWhere('out_status', '!=', 0);
                $query->orWhere('pay_status', '!=', 0);
            })->first();
        $result['total_money'] = empty($moneyInfo)?0:$moneyInfo['total_money'];

        $result['sell_info'] = SellModel::select(
            'id',
            'final_money',
            'sell_date'
        )
            ->where([
                'repeal_status' => 0,
            ])
            ->where($condition)
            ->where(function($query) {
                $query->orWhere('out_status', '!=', 0);
                $query->orWhere('pay_status', '!=', 0);
            })
            ->orderBy('sell_date', 'DESC')
            ->paginate($data['pageSize'],['*'], '',$data['currentPage']);

        return $result;
    }

    //仓库报表
    static public function statementStorage($condition)
    {
        $result['storage_info'] = StorageModel::select(
            'id',
            'storage_name',
            'storage_address'
        )
            ->where($condition)
            ->where('delete_flag', 0)
            ->get()->toArray();

        $storageManageInfo = StorageManageModel::select(
            'storage_id',
            DB::raw('SUM(in_storage_num) as total_amount'),
            DB::raw('SUM(in_storage_num * materiel_mean_cost) as total_cost')
        )
            ->where('delete_flag', 0)
            ->where($condition)
            ->groupBy('storage_id')
            ->get()->toArray();

        foreach($result['storage_info'] as $key => &$value) {
            foreach($storageManageInfo as $val) {
                if($value['id'] == $val['storage_id']) {
                    $value['storage_detail_info'] = [
                        'total_amount' => $val['total_amount'],
                        'total_cost' => $val['total_cost']
                    ];
                }
            }
        }
        return $result;
    }

    //仓库报表详细
    static public function statementStorageDetail($storageId, $data)
    {
        $condition = [];
        if(!empty($storageId)) {
            $condition['storage_id'] = ['' => ['EQ' => $storageId]];
        }

        $result['detail_info'] = StorageManageModel::select(
            'type_id',
            'b.materiel_name',
            'b.type_name',
            'b.materiel_no',
            DB::raw('SUM(in_storage_num) as total_amount'),
            DB::raw('SUM(in_storage_num * materiel_mean_cost) as total_cost')
        )
            ->from('storage_manage as a')
            ->leftJoin('materiel_type as b', 'b.id', '=', 'a.type_id')
            ->where('a.main_id', $data['main_id'])
            ->where($condition)
            ->where('a.delete_flag',0)
            ->where('b.delete_flag',0)
            ->groupBy('type_id')            
            ->paginate($data['pageSize'],['*'], '',$data['currentPage']);

        return $result;
    }

    static public function statementCustomer($data, $condition)
    {
        //采购信息
        $materielInfo = MaterielModel::select(
            'id',
            'supplier_customer_id as customer_id',
            'supplier_name as customer_name'
        )
            ->where([
                'repeal_status' => 0,
            ]);
        if(!empty($data['keyword'])) {
            $materielInfo->where('supplier_name', 'LIKE', '%'.$data['keyword'].'%');
        }
        $materielInfo = $materielInfo->where(function($query) {
                /* $query->orWhere('pay_status', '!=', 0);
                $query->orWhere('in_storage_status', '!=', 0); */
            })

            ->where($condition)
            ->get()->toArray();
        //销售信息
        $sellInfo = SellModel::select(
            'id',
            'customer_id',
            'customer_name'
        )
            ->where('repeal_status', 0);
            if(!empty($data['keyword'])) {
                $sellInfo->where('customer_name', 'LIKE', '%'.$data['keyword'].'%');
            }
            $sellInfo = $sellInfo->where(function($query) {
               /*  $query->orWhere('out_status', '!=', 0);
                $query->orWhere('pay_status', '!=', 0); */
            })
           ->where($condition)
            ->get()->toArray();
        //销售信息
        $salesInfo = SalesReturnModel::select(
            'id',
            'customer_id',
            'customer_name'
        )
            ->where('repeal_status', 0);
            if(!empty($data['keyword'])) {
                $salesInfo->where('customer_name', 'LIKE', '%'.$data['keyword'].'%');
            }
        $salesInfo = $salesInfo->where(function($query) {
                /* $query->orWhere('out_status', '!=', 0);
                $query->orWhere('pay_status', '!=', 0); */
            })

            ->where($condition)
            ->get()->toArray();

        $materielId = [];
        $materielSupplierInfo = [];
        foreach($materielInfo as $value) {
            $materielId[] = $value['id'];
            $materielSupplierInfo[] = [
                'customer_name' => $value['customer_name'],
                'customer_id' => $value['customer_id']
            ];
        }

        $sellId = [];
        $sellCustomerInfo = [];
        foreach($sellInfo as $val) {
            $sellId[] = $val['id'];
            $sellCustomerInfo[] = [
                'customer_name' => $val['customer_name'],
                'customer_id' => $val['customer_id']
            ];
        }

        $salesReturnId = [];
        $salesCustomerInfo = [];
        foreach($salesInfo as $v) {
            $salesReturnId[] = $v['id'];
            $salesCustomerInfo[] = [
                'customer_name' => $v['customer_name'],
                'customer_id' => $v['customer_id']
            ];
        }

        $datas = array_merge_recursive(
            $materielSupplierInfo,
            $sellCustomerInfo,
            $salesCustomerInfo
        );

        $arr = [];
        foreach($datas as $kkkk => $vals) {
            if(in_array($vals['customer_id'], $arr)) {
                unset($datas[$kkkk]);
            }
            else {
                $arr[] = $vals['customer_id'];
            }
        }

        //总应付金额-采购
        $materielShouldPayInfo = SELF::materielShouldPayInfo($materielId);
        //销售-总应付金额
        $sellShouldPayInfo = SELF::sellShouldPayInfo($sellId);
        //实付金额-销售
        $sellFinalPayInfo = SELF::sellFinalPayMoney($sellId);

        //退货-应付金额
        $salesReturnPayInfo =  SELF::salesReturnPayMoney($salesReturnId);

        $result = [];
        foreach($datas as $k1 => $v1) {
            $result[$k1]['customer_id'] = $v1['customer_id'];
            $result[$k1]['customer_name'] = $v1['customer_name'];
            $result[$k1]['materiel_money'] = 0;
            $result[$k1]['sell_money'] = 0;
            $result[$k1]['income_money'] = 0;
            $result[$k1]['customer_debt'] = 0;
            $result[$k1]['sales_return_money'] = 0;
        }

        $debtInfo = [];
        foreach($sellShouldPayInfo as $key => $vvv1) {
            foreach($sellFinalPayInfo as $vvv2) {
                if($vvv1['customer_id'] == $vvv2['customer_id']) {
                    $debtInfo[$key] = [
                        'customer_name' => $vvv2['customer_name'],
                        'customer_debt' => $vvv1['sell_money'] - $vvv2['final_pay_money']
                    ];
                }
            }
        }

        foreach($result as &$vv1) {
            if(!empty($materielShouldPayInfo)) {
                foreach($materielShouldPayInfo as $vv2) {
                    if($vv2['customer_id'] == $vv1['customer_id']) {
                        $vv1['materiel_money'] = $vv2['materiel_money'];
                    }
                }
            }

            if(!empty($sellShouldPayInfo)) {
                foreach($sellShouldPayInfo as $vv3) {
                    if($vv3['customer_id'] == $vv1['customer_id']) {
                        $vv1['sell_money'] = $vv3['sell_money'];
                    }
                }
            }

            if(!empty($debtInfo)) {
                foreach($debtInfo as $vv4) {
                    if($vv4['customer_name'] == $vv1['customer_name']) {
                        $vv1['customer_debt'] = sprintf('%.6f', $vv4['customer_debt']);
                    }
                }
            }

            if(!empty($salesReturnPayInfo)) {
                foreach($salesReturnPayInfo as $vv5) {
                    if($vv5['customer_id'] == $vv1['customer_id']) {
                        $vv1['sales_return_money'] = $vv5['sales_return_money'];
                    }
                }
            }
        }

        $result = array_values($result);
        foreach($result as &$v11) {

            $v11['income_money'] = $v11['sell_money'] - ($v11['materiel_money'] + $v11['sales_return_money']);
            unset($v11['sales_return_money']);
        }

        if(!empty($result)){
            foreach ($result as &$v){
                $info=SupplierCustomerModel::where('id',$v['customer_id'])->where('delete_flag',0)->first();
                if(!empty($info)) $tjList[]=$v;
            }
            $result=$tjList;
        }

        $returnResult = array_slice($result, --$data['currentPage'] * $data['pageSize'], $data['pageSize']);

        if(count($result) < $data['pageSize']) {
            $finalResult['next_page_url'] = null;
        }
        else {
            $finalResult['next_page_url'] = empty($returnResult)?null:'asdfa';
        }
        $finalResult['total'] = count($result);
        $finalResult['customer_report'] = $returnResult;

        return $finalResult;
    }

    private static function salesReturnPayMoney($salesReturnId)
    {
        return SalesReturnModel::select(
            'customer_id',
            'customer_name',
            DB::raw('SUM(final_money) as sales_return_money')
        )
            ->where('repeal_status', 0)
            ->whereIn('id', $salesReturnId)
            ->where(function($query) {
              /*   $query->orWhere('out_status', '!=', 0);
                $query->orWhere('pay_status', '!=', 0); */
            })
            ->groupBy('customer_id')
            ->groupBy('customer_name')
            ->get()->toArray();
    }

    //销售单应付金额
    static private function sellShouldPayInfo($sellId)
    {
        return SellModel::select(
            'customer_id',
            'customer_name',
            DB::raw('SUM(final_money) as sell_money')
        )
            ->where('repeal_status', 0)
            ->whereIn('id', $sellId)
          /*   ->where(function($query) {
                $query->orWhere('out_status', '!=', 0);
                $query->orWhere('pay_status', '!=', 0);
            }) */
            ->groupBy('customer_id')
            ->groupBy('customer_name')
            ->get()->toArray();
    }

    //实付金额-销售
    static private function sellFinalPayMoney($sellId)
    {
        return SellModel::select(
            'customer_id',
            'customer_name',
            DB::raw('SUM(expend) as final_pay_money')
        )
            ->from('sell as a')
            ->leftJoin('sell_pay_record as b', 'a.id', '=', 'b.sell_id')
            ->where('repeal_status', 0)
            ->whereIn('a.id', $sellId)
          /*   ->where(function($query) {
                $query->orWhere('out_status', '!=', 0);
                $query->orWhere('pay_status', '!=', 0);
            }) */
            ->groupBy('customer_id')
            ->groupBy('customer_name')
            ->get()->toArray();
    }

    //采购单应付金额
    static private function materielShouldPayInfo($materielId)
    {
        return MaterielModel::select(
            'supplier_customer_id as customer_id',
            'supplier_name',
            DB::raw('SUM(final_money) as materiel_money')
        )
            ->where('repeal_status', 0)
            ->whereIn('id', $materielId)
            ->where(function($query) {
              /*   $query->orWhere('pay_status', '!=', 0);
                $query->orWhere('in_storage_status', '!=', 0); */
            })
            ->groupBy('supplier_customer_id')
            ->groupBy('supplier_name')
            ->get()->toArray();
    }

    //客户订单详细
    static public function statementCustomerDetail($data, $condition)
    {
        //获取所有相关采购单id
        $getCustomerMaterielId= SELF::getCustomerMaterielId($data, $condition);

        $materielId = [];
        foreach($getCustomerMaterielId as $value) {
            $materielId[] = $value['id'];
        }

        if(empty($materielId)) {
            $arr['materiel_total_num'] = 0;
            $arr['materiel_total_money'] = 0;
        }
        else {
            //获取采购单总金额-总实付金额
            $getCustomerMaterielFinalMoney = SELF::getCustomerMaterielFinalMoney($materielId);
            //获取采购单内容信息-数量
            $getCustomerMaterielContentInfo = SELF::getCustomerMaterielContentInfo($materielId);

            $arr['materiel_total_num'] = $getCustomerMaterielContentInfo['total_materiel_num'];
            $arr['materiel_total_money'] = $getCustomerMaterielFinalMoney['total_final_money'];
        }

        //获取所有销售单id信息
        $getCustomerSellId = SELF::getCustomerSellId($data, $condition);

        $sellId = [];
        foreach($getCustomerSellId as $val) {
            $sellId[] = $val['id'];
        }

        if(empty($sellId)) {
            $arr['sell_total_num'] = 0;
            $arr['sell_final_money'] = 0;
            $arr['sell_actual_pay'] = 0;
            $arr['sell_debt'] = 0;
            $arr['total_income'] = 0;
        }
        else {
            //销售单总销售金额
            $getCustomerSellMoney = SELF::getCustomerSellMoney($sellId);

            //销售单商品总件数
            $getCustomerSellMaterielAmountInfo = SELF::getCustomerSellMaterielAmountInfo($sellId);
            //销售单套餐总件数
            $getCustomerSellPackageAmountInfo = SELF::getCustomerSellPackageAmountInfo($sellId);
            //销售单实付金额
            $getCustomerSellFinalMoney = SELF::getCustomerSellFinalMoney($sellId);
            //退货信息-总金额
            $getCustomerSalesReturnInfo = SELF::getCustomerSalesReturnInfo($data, $condition);

            $salesReturnTotalMoney = empty($getCustomerSalesReturnInfo['sales_return_money'])?0:$getCustomerSalesReturnInfo['sales_return_money'];

            $sellMaterielNum = empty($getCustomerSellMaterielAmountInfo)?0:$getCustomerSellMaterielAmountInfo['sell_materiel_total_num'];
            $sellPackageNum = empty($getCustomerSellPackageAmountInfo)?0:$getCustomerSellPackageAmountInfo['sell_package_total_num'];
            $arr['sell_total_num'] = $sellMaterielNum + $sellPackageNum;//总商品数量
            $arr['sell_final_money'] = $getCustomerSellMoney['total_final_money'];//订单总额
            //订单实付
            $arr['sell_actual_pay'] = empty($getCustomerSellFinalMoney['sell_final_pay_money'])?0:$getCustomerSellFinalMoney['sell_final_pay_money'];
            //总欠款
            $arr['sell_debt'] = sprintf('%.6f', $arr['sell_final_money'] - $arr['sell_actual_pay']);

            $totalIncode = $arr['sell_final_money'] - $arr['materiel_total_money'] - $salesReturnTotalMoney;
            $arr['total_income'] = sprintf('%.6f', $totalIncode);//总利润
        }

        //统计类型;0:采购;1:销售;
        if($data['type'] == 0) {
            $result = SELF::getMaterielList($data, $condition, $materielId);
        }
        else {
            $result = SELF::getSellList($data, $condition, $sellId);
        }

        $arr['list_info'] = $result;
        return $arr;
    }

    static private function getSellList($data, $condition, $sellId)
    {
        $sellInfo = SellModel::select(
            'id',
            'order_no',
            'sell_date',
            'final_money'
        )
            ->where($condition)
            ->where('repeal_status', 0)
            ->whereIn('id', $sellId)
            ->orderBy('id', 'DESC')
            ->paginate($data['pageSize'],['*'], '',$data['currentPage'])->toArray();

        foreach($sellInfo['data'] as &$value) {
            $sellPayInfo = SELF::getSingleSellPayInfo($value['id']);

            $value['sell_num'] = SELF::getSingleSellTotalNum($value['id']);
            $value['debt'] = sprintf('%.6f', $value['final_money'] - $sellPayInfo['total_money']);//欠款
        }

        return $sellInfo;
    }

    static private function getSingleSellTotalNum($sellId) {
        $sellInfo = SellGoodsDetailModel::select(
            DB::raw('SUM(num) as sell_matereil_num')
        )
            ->where('sell_id', $sellId)
            ->first();

        $sellPackageInfo = SellPackageDetailModel::select(
            DB::raw('SUM(num) as sell_package_num')
        )
            ->where('sell_id', $sellId)
            ->first();

        $sellMaterielNum = empty($sellInfo['sell_matereil_num'])?0:$sellInfo['sell_matereil_num'];
        $sellPackageNum = empty($sellPackageInfo['sell_package_num'])?0:$sellPackageInfo['sell_package_num'];

        return $sellMaterielNum + $sellPackageNum;
    }

    static private function getSingleSellPayInfo($sellId)
    {
        return SellPayRecordModel::select(
            DB::raw('SUM(expend) as total_money')
        )
            ->where('sell_id', $sellId)->first();
    }

    //获取供应商或客户采购信息
    static private function getMaterielList($data, $condition, $materielId)
    {
        $materielInfo =  MaterielModel::select(
            'id',
            'purchase_no',
            'purchase_time',
            'final_money'
        )
            ->where($condition)
            ->where('repeal_status', 0)
            ->whereIn('id', $materielId)
            ->orderBy('id', 'DESC')
            ->paginate($data['pageSize'],['*'], '',$data['currentPage'])->toArray();

        foreach($materielInfo['data'] as &$value) {
            $materielPayInfo = SELF::getSingleMaterielPayInfo($value['id']);

            //采购单采购总数
            $value['materiel_num'] = SELF::getSingleMatereilNum($value['id']);
            $value['debt'] = sprintf('%.6f', $value['final_money'] - $materielPayInfo['total_money']);//欠款
        }

        return $materielInfo;
    }

    //获取采购单采购总数
    static private function getSingleMatereilNum($id)
    {
        $info = MaterielContentModel::select(
            DB::raw('SUM(num) as total_num')
        )
            ->where('materiel_id', $id)->first();

        return empty($info['total_num'])?0:$info['total_num'];
    }

    static private function getSingleMaterielPayInfo($materielId)
    {
        return MaterielPayHistoryModel::select(
            DB::raw('SUM(money) as total_money')
        )
            ->where('materiel_id', $materielId)->first();
    }

    //退货信息-总金额
    static private function getCustomerSalesReturnInfo($data, $condition)
    {
        return SalesReturnModel::select(
            DB::raw('SUM(final_money) as sales_return_money')
        )
            ->where($condition)
            ->where('customer_id', $data['customer_id'])
            ->where('customer_name', $data['customer_name'])
            ->where('repeal_status', 0)
            ->get()->toArray();
    }

    //销售单实付金额
    static private function getCustomerSellFinalMoney($sellId)
    {
        return SellPayRecordModel::select(
            DB::raw('SUM(expend) as sell_final_pay_money')
        )
        ->whereIn('sell_id', $sellId)
            ->first();
    }

    //销售单套餐总件数
    static private function getCustomerSellPackageAmountInfo($sellId)
    {
        return SellPackageDetailModel::select(
            DB::raw('SUM(num) as sell_package_total_num')
        )
            ->whereIn('sell_id', $sellId)
            ->first();
    }

    //销售单商品总件数
    static private function getCustomerSellMaterielAmountInfo($sellId)
    {
        return SellGoodsDetailModel::select(
            DB::raw('SUM(num) as sell_materiel_total_num')
        )
            ->whereIn('sell_id', $sellId)
            ->first();
    }

    //销售单总销售金额
    static private function getCustomerSellMoney($sellId)
    {
        return SellModel::select(
            DB::raw('SUM(final_money) as total_final_money')
        )
            ->whereIn('id', $sellId)
            ->first();
    }

    //获取所有销售单id信息
    static private function getCustomerSellId($data, $condition)
    {
        return SellModel::select(
            'id',
        )
            ->where($condition)
            ->where('customer_id', $data['customer_id'])
            ->where('customer_name', $data['customer_name'])
            ->where('repeal_status', 0)
            ->get()->toArray();
    }

    //获取采购单总金额
    static private function getCustomerMaterielFinalMoney($materielId)
    {
        return MaterielModel::select(
            DB::raw('SUM(final_money) as total_final_money'),
        )
            ->whereIn('id', $materielId)
            ->groupBy('supplier_customer_id')
            ->first();
    }

    //获取采购单内容信息
    static private function getCustomerMaterielContentInfo($materielId) {
        return MaterielContentModel::select(
            'materiel_id',
            DB::raw('SUM(num) as total_materiel_num'),
        )
            ->whereIn('materiel_id', $materielId)
            ->groupBy('materiel_id')
            ->first();
    }

    //获取采购单信息
    static private function getCustomerMaterielId($data, $condition)
    {
        return MaterielModel::select(
            'id',
        )
            ->where($condition)
            ->where('supplier_customer_id', $data['customer_id'])
            ->where('supplier_name', $data['customer_name'])
            ->where('repeal_status', 0)
            ->get()->toArray();
    }

    private static function getMaterielInfo($data, $condition)
    {
        return MaterielContentModel::select(
            DB::raw('SUM(num) as total_materiel_num'),
            DB::raw('SUM(final_money) as total_materiel_money')
        )
            ->from('materiel_content as a')
            ->leftJoin('materiel as b', 'b.id', '=', 'a.materiel_id')
            ->where($condition)
            ->where('b.supplier_customer_id', $data['customer_id'])
            ->where('b.supplier_name', $data['customer_name'])
            ->where('b.repeal_status', 0)
            ->first();
    }

    //采购商品统计
    static public function statisticsPurchasedGoods($condition, $data,$keyword)
    {

        $amountInfo = MaterielContentModel::selectRaw('
            SUM(num) as total_amount
        ')
            ->from('materiel_content as a')
            ->leftJoin('materiel as b', 'b.id', '=', 'a.materiel_id')
            ->where('b.repeal_status', 0)
            ->where($condition)
            ->where('b.supplier_customer_id',$data['customer_id'])
            ->where(function($query) {
                $query->orWhere('b.pay_status', '!=', 0);
                $query->orWhere('b.in_storage_status', '!=', 0);
            })
             ->where(function($query) use ($keyword) {
                $query->orWhere('a.materiel_name', 'like', '%'.$keyword.'%');
                $query->orWhere('a.type_name', 'like', '%'.$keyword.'%');
                $query->orWhere('a.materiel_no', 'like', '%'.$keyword.'%');

            })
            ->groupBy('a.main_id')
            ->first();
            $result['total_amount'] = empty($amountInfo)?0:$amountInfo['total_amount'];

            $priceInfo = MaterielContentModel::selectRaw('
            SUM(num * unit_price) as total_price
        ')
            ->from('materiel_content as a')
            ->leftJoin('materiel as b', 'b.id', '=', 'a.materiel_id')
            ->where('b.repeal_status', 0)
            ->where($condition)
            ->where('b.supplier_customer_id',$data['customer_id'])
            ->where(function($query) {
                $query->orWhere('b.pay_status', '!=', 0);
                $query->orWhere('b.in_storage_status', '!=', 0);
            })
            ->where(function($query) use ($keyword) {
                $query->orWhere('a.materiel_name', 'like', '%'.$keyword.'%');
                $query->orWhere('a.type_name', 'like', '%'.$keyword.'%');
                $query->orWhere('a.materiel_no', 'like', '%'.$keyword.'%');

            })
            ->groupBy('a.main_id')
            ->first();
            $result['total_price'] = empty($priceInfo)?0:$priceInfo['total_price'];


            $query = MaterielContentModel::select(
                'type_id',
                'materiel_name',
                'type_name',
               /*  'materiel_no', */
                'materiel_img',
                DB::raw('SUM(num * unit_price) as total_unit_price'),
                DB::raw('SUM(num) as num')
                )
                ->from('materiel_content as a')
                ->leftJoin('materiel as b', 'b.id', '=', 'a.materiel_id')
                ->where('b.repeal_status', 0)
                ->where($condition)
                ->where('b.supplier_customer_id',$data['customer_id'])
                ->where(function($query) {
                    $query->orWhere('b.pay_status', '!=', 0);
                    $query->orWhere('b.in_storage_status', '!=', 0);
                })
                ->where(function($query) use ($keyword) {
                    $query->orWhere('a.materiel_name', 'like', '%'.$keyword.'%');
                    $query->orWhere('a.type_name', 'like', '%'.$keyword.'%');
                    $query->orWhere('a.materiel_no', 'like', '%'.$keyword.'%');
                });
                if(!empty($data['number_sort'])){
                    $query->orderBy('num',$data['number_sort']);
                }
                if(!empty($data['price_sort'])){

                    $query -> orderBy('total_unit_price',$data['price_sort']);
                }

               $result['materiel_content_info'] = $query ->groupBy('type_id')->paginate($data['pageSize'],['*'], '',$data['currentPage']);

                return $result;
    }

    static public function sellGoodsList($condition, $data,$keyword)
    {
        //获取所有销售单id信息
        $getCustomerSellId =SellModel::select('id')
        ->from('sell as a')
            ->where($condition)
            ->where('customer_id', $data['customer_id'])
            ->where('repeal_status', 0)
            ->where(function($query) {
                $query->orWhere('out_status', '!=', 0);
                $query->orWhere('pay_status', '!=', 0);
            })->get()->toArray();

        $sellId = [];
        foreach($getCustomerSellId as $val) {
            $sellId[] = $val['id'];
        }
        if(empty($sellId)) {
            $result['sell_final_money'] = 0;
        }else {
            //销售单总销售金额
            $result = SellModel::select(DB::raw('SUM(final_money) as sell_final_money'))
            ->whereIn('id', $sellId)->first();
        }

        /* DB::enableQueryLog(); */
       $query = SellGoodsListModel::select(
            'b.materiel_name',
            'b.type_name',
            'b.materiel_img',
            DB::raw('SUM(num) as total_num'),
            DB::raw('SUM(cost_price * num) as total_cost')
            )
            ->from('sell_goods_list as a')
            ->leftJoin('materiel_type as b', 'b.id', '=', 'a.materiel_type_id')
            ->where($condition)
            ->whereIn('a.sell_id',$sellId)
            ->where(function($query) use ($keyword) {
                $query->orWhere('b.materiel_name', 'like', '%'.$keyword.'%');
                $query->orWhere('b.type_name', 'like', '%'.$keyword.'%');
            });
            if(!empty($data['number_sort'])){
                $query->orderBy('total_num',$data['number_sort']);
            }
            if(!empty($data['price_sort'])){
                $query ->orderBy('cost_price',$data['price_sort']);
            }
             $result['sell_goods_list']=$query->groupBy('a.materiel_type_id')->paginate($data['pageSize'],['*'], '',$data['currentPage'])->toArray();      
             if(empty($result['sell_goods_list']['data'])){
                 $result['sell_final_money'] = 0;
             }
             return $result;

    }


}
